const { Course, CourseCategory, UserCourseProgress, User } = require('./model');
const { Op } = require('sequelize');

class CourseDao {
  // 创建课程
  async createCourse(courseData) {
    try {
      return await Course.create(courseData);
    } catch (error) {
      throw new Error(`创建课程失败: ${error.message}`);
    }
  }

  // 根据ID查找课程
  async findById(id) {
    try {
      return await Course.findByPk(id, {
        include: [{
          model: CourseCategory,
          as: 'category'
        }]
      });
    } catch (error) {
      throw new Error(`查找课程失败: ${error.message}`);
    }
  }

  // 获取课程列表（分页）
  async getCourseList(page = 1, limit = 10, filters = {}) {
    try {
      const offset = (page - 1) * limit;
      const where = {};

      // 构建查询条件
      if (filters.category_id) {
        where.category_id = filters.category_id;
      }
      if (filters.difficulty) {
        where.difficulty = filters.difficulty;
      }
      if (filters.is_free !== undefined) {
        where.is_free = filters.is_free;
      }
      if (filters.status) {
        where.status = filters.status;
      }
      if (filters.keyword) {
        where[Op.or] = [
          { title: { [Op.like]: `%${filters.keyword}%` } },
          { description: { [Op.like]: `%${filters.keyword}%` } }
        ];
      }

      const { count, rows } = await Course.findAndCountAll({
        where,
        limit,
        offset,
        order: [['sort_order', 'ASC'], ['created_at', 'DESC']],
        include: [{
          model: CourseCategory,
          as: 'category'
        }]
      });

      return {
        total: count,
        list: rows,
        page,
        limit
      };
    } catch (error) {
      throw new Error(`获取课程列表失败: ${error.message}`);
    }
  }

  // 获取推荐课程
  async getRecommendedCourses(limit = 10) {
    try {
      return await Course.findAll({
        where: {
          status: 'published'
        },
        order: [['rating', 'DESC'], ['view_count', 'DESC']],
        limit,
        include: [{
          model: CourseCategory,
          as: 'category'
        }]
      });
    } catch (error) {
      throw new Error(`获取推荐课程失败: ${error.message}`);
    }
  }

  // 更新课程
  async updateCourse(id, updateData) {
    try {
      const [affectedRows] = await Course.update(updateData, {
        where: { id }
      });
      if (affectedRows === 0) {
        throw new Error('课程不存在');
      }
      return await this.findById(id);
    } catch (error) {
      throw new Error(`更新课程失败: ${error.message}`);
    }
  }

  // 增加观看次数
  async incrementViewCount(id) {
    try {
      return await Course.increment('view_count', {
        where: { id }
      });
    } catch (error) {
      throw new Error(`更新观看次数失败: ${error.message}`);
    }
  }

  // 增加点赞数
  async incrementLikeCount(id) {
    try {
      return await Course.increment('like_count', {
        where: { id }
      });
    } catch (error) {
      throw new Error(`更新点赞数失败: ${error.message}`);
    }
  }

  // 增加报名人数
  async incrementEnrollmentCount(id) {
    try {
      return await Course.increment('enrollment_count', {
        where: { id }
      });
    } catch (error) {
      throw new Error(`更新报名人数失败: ${error.message}`);
    }
  }

  // 删除课程
  async deleteCourse(id) {
    try {
      const affectedRows = await Course.destroy({
        where: { id }
      });
      if (affectedRows === 0) {
        throw new Error('课程不存在');
      }
      return true;
    } catch (error) {
      throw new Error(`删除课程失败: ${error.message}`);
    }
  }

  // 创建课程分类
  async createCategory(categoryData) {
    try {
      return await CourseCategory.create(categoryData);
    } catch (error) {
      throw new Error(`创建分类失败: ${error.message}`);
    }
  }

  // 获取所有分类
  async getAllCategories() {
    try {
      return await CourseCategory.findAll({
        where: { status: 'active' },
        order: [['sort_order', 'ASC'], ['created_at', 'ASC']]
      });
    } catch (error) {
      throw new Error(`获取分类失败: ${error.message}`);
    }
  }

  // 获取课程分类（包含课程数量统计）
  async getCourseCategories() {
    try {
      return await CourseCategory.findAll({
        where: { is_active: true },
        attributes: [
          'id',
          'name',
          'description',
          'icon',
          'color',
          'sort',
          [CourseCategory.sequelize.fn('COUNT', CourseCategory.sequelize.col('courses.id')), 'course_count']
        ],
        include: [{
          model: Course,
          as: 'courses',
          attributes: [],
          where: { is_active: true },
          required: false
        }],
        group: ['course_categories.id', 'course_categories.name', 'course_categories.description', 'course_categories.icon', 'course_categories.color', 'course_categories.sort'],
        order: [['sort', 'ASC'], ['created_at', 'ASC']]
      });
    } catch (error) {
      throw new Error(`获取课程分类失败: ${error.message}`);
    }
  }

  // 根据分类ID查找分类
  async findCategoryById(id) {
    try {
      return await CourseCategory.findByPk(id);
    } catch (error) {
      throw new Error(`查找分类失败: ${error.message}`);
    }
  }

  // 更新分类
  async updateCategory(id, updateData) {
    try {
      const [affectedRows] = await CourseCategory.update(updateData, {
        where: { id }
      });
      if (affectedRows === 0) {
        throw new Error('分类不存在');
      }
      return await this.findCategoryById(id);
    } catch (error) {
      throw new Error(`更新分类失败: ${error.message}`);
    }
  }

  // 删除分类
  async deleteCategory(id) {
    try {
      // 检查是否有课程使用该分类
      const courseCount = await Course.count({
        where: { category_id: id }
      });
      if (courseCount > 0) {
        throw new Error('该分类下还有课程，无法删除');
      }

      const affectedRows = await CourseCategory.destroy({
        where: { id }
      });
      if (affectedRows === 0) {
        throw new Error('分类不存在');
      }
      return true;
    } catch (error) {
      throw new Error(`删除分类失败: ${error.message}`);
    }
  }

  // 获取课程（支持多种查询条件）
  async getCourses(options = {}) {
    try {
      const {
        page = 1,
        limit = 10,
        category_id,
        difficulty,
        is_active = true,
        is_featured,
        keyword
      } = options;

      const offset = (page - 1) * limit;
      const where = {};

      // 构建查询条件
      if (is_active !== undefined) where.is_active = is_active;
      if (category_id) where.category_id = category_id;
      if (difficulty) where.difficulty = difficulty;
      if (is_featured !== undefined) where.is_featured = is_featured;

      if (keyword) {
        where[Op.or] = [
          { title: { [Op.like]: `%${keyword}%` } },
          { description: { [Op.like]: `%${keyword}%` } }
        ];
      }

      const { count, rows } = await Course.findAndCountAll({
        where,
        limit: parseInt(limit),
        offset: parseInt(offset),
        order: [['is_featured', 'DESC'], ['rating', 'DESC'], ['student_count', 'DESC'], ['created_at', 'DESC']],
        include: [{
          model: CourseCategory,
          as: 'category'
        }]
      });

      return {
        courses: rows,
        total: count,
        page: parseInt(page),
        limit: parseInt(limit),
        totalPages: Math.ceil(count / limit)
      };
    } catch (error) {
      throw new Error(`获取课程失败: ${error.message}`);
    }
  }

  // 搜索课程（支持复杂搜索条件）
  async searchCourses(options = {}) {
    try {
      const {
        keyword,
        page = 1,
        limit = 10,
        category_id,
        difficulty,
        instructor,
        price_min,
        price_max,
        is_active = true
      } = options;

      const offset = (page - 1) * limit;
      const where = { is_active };

      // 关键词搜索
      if (keyword && keyword.trim()) {
        where[Op.or] = [
          { title: { [Op.like]: `%${keyword.trim()}%` } },
          { description: { [Op.like]: `%${keyword.trim()}%` } },
          { instructor: { [Op.like]: `%${keyword.trim()}%` } }
        ];
      }

      // 其他筛选条件
      if (category_id) where.category_id = category_id;
      if (difficulty) where.difficulty = difficulty;
      if (instructor) where.instructor = { [Op.like]: `%${instructor}%` };

      // 价格范围
      if (price_min !== undefined || price_max !== undefined) {
        where.price = {};
        if (price_min !== undefined) where.price[Op.gte] = parseFloat(price_min);
        if (price_max !== undefined) where.price[Op.lte] = parseFloat(price_max);
      }

      const { count, rows } = await Course.findAndCountAll({
        where,
        limit: parseInt(limit),
        offset: parseInt(offset),
        order: [['rating', 'DESC'], ['student_count', 'DESC'], ['created_at', 'DESC']],
        include: [{
          model: CourseCategory,
          as: 'category'
        }]
      });

      return {
        courses: rows,
        total: count,
        page: parseInt(page),
        limit: parseInt(limit),
        totalPages: Math.ceil(count / limit)
      };
    } catch (error) {
      throw new Error(`搜索课程失败: ${error.message}`);
    }
  }

  // 用户课程进度相关操作

  // 创建或更新用户课程进度
  async upsertUserCourseProgress(userId, courseId, progressData) {
    try {
      const [progress, created] = await UserCourseProgress.upsert({
        user_id: userId,
        course_id: courseId,
        ...progressData
      });

      return { progress, created };
    } catch (error) {
      throw new Error(`创建或更新用户课程进度失败: ${error.message}`);
    }
  }

  // 获取用户课程进度
  async getUserCourseProgress(userId, courseId) {
    try {
      return await UserCourseProgress.findOne({
        where: {
          user_id: userId,
          course_id: courseId
        },
        include: [{
          model: Course,
          as: 'course'
        }]
      });
    } catch (error) {
      throw new Error(`获取用户课程进度失败: ${error.message}`);
    }
  }

  // 获取用户所有课程进度
  async getUserAllCourseProgress(userId, options = {}) {
    try {
      const { page = 1, limit = 10, status } = options;
      const offset = (page - 1) * limit;

      const whereClause = { user_id: userId };
      if (status) whereClause.status = status;

      const { count, rows } = await UserCourseProgress.findAndCountAll({
        where: whereClause,
        include: [{
          model: Course,
          as: 'course',
          include: [{
            model: CourseCategory,
            as: 'category'
          }]
        }],
        order: [['updated_at', 'DESC']],
        limit: parseInt(limit),
        offset: parseInt(offset)
      });

      return {
        progress: rows,
        total: count,
        page: parseInt(page),
        limit: parseInt(limit),
        totalPages: Math.ceil(count / limit)
      };
    } catch (error) {
      throw new Error(`获取用户所有课程进度失败: ${error.message}`);
    }
  }

  // 更新用户课程进度
  async updateUserCourseProgress(userId, courseId, updateData) {
    try {
      const [updatedRowsCount] = await UserCourseProgress.update(updateData, {
        where: {
          user_id: userId,
          course_id: courseId
        }
      });

      if (updatedRowsCount === 0) {
        throw new Error('用户课程进度不存在');
      }

      return await this.getUserCourseProgress(userId, courseId);
    } catch (error) {
      throw new Error(`更新用户课程进度失败: ${error.message}`);
    }
  }

  // 完成课程
  async completeCourse(userId, courseId) {
    try {
      return await this.updateUserCourseProgress(userId, courseId, {
        status: 'completed',
        progress: 100,
        completed_at: new Date()
      });
    } catch (error) {
      throw new Error(`完成课程失败: ${error.message}`);
    }
  }

  // 获取课程学习统计
  async getCourseStats(courseId) {
    try {
      const totalStudents = await UserCourseProgress.count({
        where: { course_id: courseId }
      });

      const completedStudents = await UserCourseProgress.count({
        where: {
          course_id: courseId,
          status: 'completed'
        }
      });

      const avgProgress = await UserCourseProgress.findOne({
        where: { course_id: courseId },
        attributes: [
          [UserCourseProgress.sequelize.fn('AVG', UserCourseProgress.sequelize.col('progress')), 'avgProgress']
        ],
        raw: true
      });

      return {
        totalStudents,
        completedStudents,
        completionRate: totalStudents > 0 ? (completedStudents / totalStudents * 100).toFixed(2) : 0,
        averageProgress: parseFloat(avgProgress?.avgProgress || 0).toFixed(2)
      };
    } catch (error) {
      throw new Error(`获取课程学习统计失败: ${error.message}`);
    }
  }
}

module.exports = new CourseDao();
